IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'MD3.CreateForeignKey') AND type IN (N'P', N'PC'))
DROP PROCEDURE MD3.CreateForeignKey
GO
--Creates a FK

CREATE PROCEDURE MD3.CreateForeignKey
	( 
	  @SchemaName		SYSNAME
	 ,@TableName		SYSNAME
     ,@KeyColumns		VARCHAR(MAX) = NULL -- comma-separated list of key columns
	 ,@ForeignKeyName	SYSNAME		 
	 ,@ParentSchemaName SYSNAME		= NULL
	 ,@ParentTableName	SYSNAME		= NULL
	 ,@ParentKeyColumns	VARCHAR(MAX) = NULL -- comma-separated list of parent table key columns
	 ,@IsDelCascade		BIT = 0      -- default is 0 (NO ACTION on delete) or 1 for CASCADE delete to turn on
	 ,@IsUpdCascade		BIT = 0      -- default is 0 (NO ACTION on update) or 1 for CASCADE update to turn on
	 ,@AllowNoCheck		BIT = 0      -- Allow NOCHECK (ie, existing data may violate FK constraint)
	 ,@DropFK			BIT = 0		 -- drop the FK based on name, do no other checking.   	
    )
    
AS      
BEGIN

	SET NOCOUNT ON    

	DECLARE 
			@Msg              VARCHAR(Max),
			@SQL              VARCHAR(Max);
		
	SELECT @SchemaName = MD3.f_RemoveBrackets (@SchemaName);
	SELECT @TableName = MD3.f_RemoveBrackets (@TableName);
	SELECT @KeyColumns = MD3.f_RemoveBrackets (@KeyColumns);
	SELECT @ForeignKeyName = MD3.f_RemoveBrackets (@ForeignKeyName);
	SELECT @ParentSchemaName = MD3.f_RemoveBrackets (@ParentSchemaName);
	SELECT @ParentTableName = MD3.f_RemoveBrackets (@ParentTableName);
	SELECT @ParentKeyColumns = MD3.f_RemoveBrackets (@ParentKeyColumns);
	DECLARE @CurrentFKName SYSNAME, @ErrorNumber INT;
	SELECT @CurrentFKName = ''
	DECLARE @RecreateFK BIT
	DECLARE @FKCount INT = 0
	DECLARE @FKName SYSNAME		
	DECLARE @FKList  TABLE   
		( 	ForeignKeyName		SYSNAME NOT NULL,   
			SchemaName			SYSNAME NOT NULL, 
			TableName			SYSNAME NOT NULL,
			KeyColumns			VARCHAR(MAX)NOT NULL,
			ParentSchemaName	SYSNAME NOT NULL, 
			ParentTableName		SYSNAME NOT NULL,
			ParentKeyColumns	VARCHAR(MAX)NOT NULL,
			IsDisabled          BIT NOT NULL,
			IsNotTrusted        BIT NOT NULL,
			IsNotForReplication BIT NOT NULL,
			IsDeleteCascade     BIT NOT NULL,
			IsUpdateCascade     BIT NOT NULL
		) 
			
	----------------------------------------------------------------
	-- handle @DropFK parameter 
	----------------------------------------------------------------			
	IF (@DropFK = 1)
	BEGIN
		IF EXISTS (SELECT * FROM MD3.f_GetForeignKeys(@SchemaName,@TableName) WHERE ForeignKeyName = @ForeignKeyName)
		BEGIN
			SELECT @SQL = 'ALTER TABLE ' + @SchemaName + '.' + @TableName + ' DROP CONSTRAINT ' + @ForeignKeyName

			EXEC MD3.ExecuteSQL @SQL;

			RETURN
		END
		ELSE 
		BEGIN
			--fk was previously dropped, no work to do. 
			RETURN
		END
	END
	        


	--get EVERY FK for this table
	INSERT INTO @FKList
	SELECT 
		ForeignKeyName,		
		SchemaName	,		
		TableName	,		
		KeyColumns	,		
		ParentSchemaName,	
		ParentTableName		,
		ParentKeyColumns	,
		IsDisabled        ,  
		IsNotTrusted        ,
		IsNotForReplication ,
		IsDeleteCascade     ,
		IsUpdateCascade     
	FROM MD3.f_GetForeignKeys (@SchemaName,@TableName)	  

				
			---------------------------------------------------------------------
			-- Retrieving Current FK Name, If exists with same attributes
			-- attributes are the most "important" aspects of an FK
			-- properties are the aspects that, if different, require a recreation
			-- @CurrentFKName = '' when nothing found by attributes
			---------------------------------------------------------------------
			SELECT @CurrentFKName = ForeignKeyName 
			FROM @FKList
			WHERE SchemaName			= @SchemaName 
				AND TableName			= @TableName
				AND KeyColumns			= @KeyColumns
				AND ParentSchemaName	= @ParentSchemaName 
				AND ParentTableName		= @ParentTableName
				AND ParentKeyColumns	= @ParentKeyColumns;
			
			------------------------------------------------------------------------------------
			-- Checking Current FK, If exists with same atrributes and different properties
			-- don't check the name here, we'll do that later, just want to know if it exists for now
			------------------------------------------------------------------------------------
			IF EXISTS(SELECT 1 FROM @FKList
					  WHERE SchemaName			= @SchemaName 
						 AND TableName			= @TableName
						 AND KeyColumns			= @KeyColumns
						 AND ParentSchemaName	= @ParentSchemaName 
						 AND ParentTableName	= @ParentTableName
						 AND ParentKeyColumns	= @ParentKeyColumns
						 --in this case we need to Recreate the FK with the changed properties.  
						 AND( IsDisabled = 1 OR (IsNotTrusted = 1 AND @AllowNoCheck = 0) 
								OR IsDeleteCascade <> @IsDelCascade 
								OR IsUpdateCascade <> @IsUpdCascade)) 	
				BEGIN		
					SELECT @RecreateFK = 1
				END	
			ELSE 
				BEGIN		
					SELECT @RecreateFK = 0
				END	
		
			--------------------------------------------------------------        
			-- Checking if FK already exists with same name and properties 
			--------------------------------------------------------------       
			IF (@RecreateFK = 0 AND @CurrentFKName = @ForeignKeyName)
			BEGIN
				EXEC MD3.InsertLogMessage 
					@Message = 'Specified Foreign Key already exists with identical properties.',
					@Severity = 'I'; 

				RETURN
			END

			-------------------------------------------------------------------------------     
			-- Checking if FK already exists with identical properties and different name  
			-------------------------------------------------------------------------------  
			IF (@RecreateFK = 0 AND @CurrentFKName <> @ForeignKeyName AND @CurrentFKName <> '')
			BEGIN
			
				SET @SQL='EXEC sp_rename '''+@CurrentFKName+''','''+@ForeignKeyName+'''' +',''OBJECT'''   

				EXEC MD3.ExecuteSQL @SQL
	
				RETURN
			END

			-----------------------------------------------------------------------------    
			-- Checking if FK already exists with different CASCADE options or Trusting
			-----------------------------------------------------------------------------   
			IF (@RecreateFK = 1 AND @CurrentFKName <> '')
			BEGIN
				IF @CurrentFKName = @ForeignKeyName
				BEGIN
					SELECT @SQL = 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' +QUOTENAME(@TableName)+' DROP CONSTRAINT '+ QUOTENAME(@ForeignKeyName)
						
					SET @Msg =  'Specified FK exists with same name '+ ''''+  @ForeignKeyName + '''' + ' but different options. DROP/CREATE... '
					EXEC MD3.InsertLogMessage 
						@Message = @Msg,
						@Severity = 'I';
						
					EXEC MD3.ExecuteSQL @SQL;
				END
				ELSE
				BEGIN
					SELECT @SQL = 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' +QUOTENAME(@TableName)+' DROP CONSTRAINT '+ QUOTENAME(@CurrentFKName)
						
					SET @Msg =  'Specified FK exists with different name '+ ''''+  @ForeignKeyName + '''' + ' but different options.  DROP/CREATE... ' 
					EXEC MD3.InsertLogMessage 
						@Message = @Msg,
						@Severity = 'I';
						
					EXEC MD3.ExecuteSQL @SQL;
				END 
			END
			

			-------------------------------------------------------------------------------     
			-- Modifying a Foreign Key defintion by Dropping and Recreating 
			------------------------------------------------------------------------------- 
			 IF (EXISTS(SELECT 1 FROM @FKList WHERE ForeignKeyName = @ForeignKeyName)) AND (@RecreateFK = 0) AND (@CurrentFKName = '')
			 BEGIN 
			
				SELECT @SQL = 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' +QUOTENAME(@TableName)+' DROP CONSTRAINT '+ QUOTENAME(@ForeignKeyName)
				
				SET @Msg =  'Specified FK exists with same name '''+  @ForeignKeyName + ''' but different properties. DROP/CREATE... '
				EXEC MD3.InsertLogMessage 
						@Message = @Msg,
						@Severity = 'I';
						
				EXEC MD3.ExecuteSQL @SQL;
				
				SET @RecreateFK = 1
			END		
			
			-------------------------------------------------------------------------------     
			-- Creating a Foreign Key
			------------------------------------------------------------------------------- 
					 
			IF (@RecreateFK = 1 OR @CurrentFKName = '')
			BEGIN

				--always attempt the FK creation as TRUSTed first
				--regardless of @AllowNoCheck option

				SELECT @SQL = 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' +QUOTENAME(@TableName) + ' WITH CHECK ADD CONSTRAINT '+QUOTENAME(@ForeignKeyName)+' FOREIGN KEY ('+ @KeyColumns +') REFERENCES '+  QUOTENAME(@ParentSchemaName) + '.' +QUOTENAME(@ParentTableName)+' ('+@ParentKeyColumns+')'    
				SELECT @SQL = @SQL + ' ON DELETE ' + CASE WHEN @IsDelCascade = 0 THEN 'NO ACTION'  
															ELSE 'CASCADE' END
				SELECT @SQL = @SQL + ' ON UPDATE ' + CASE WHEN @IsUpdCascade = 0 THEN 'NO ACTION'  
															ELSE 'CASCADE' END
				
				BEGIN TRY
					EXEC MD3.InsertLogMessage 
						@Message = 'Attempting to create FK as trusted.',
						@Severity = 'I';
					EXEC MD3.ExecuteSQL @SQL;
				END TRY
				BEGIN CATCH
					--goofy TSQL error handling workaround...documented in that proc.  
					EXEC MD3.LogErrorParse @ErrorNumber = @ErrorNumber OUTPUT;

					IF @ErrorNumber = 547 
					BEGIN
						IF @AllowNoCheck = 0
						BEGIN
							EXEC MD3.InsertLogMessage 
								@Message = @SQL,
								@Severity = 'E';
							RAISERROR ('Could not add trusted FK constraint.',16,1);
							RETURN 1;
						END
						ELSE 
						BEGIN
							--let's try again WITH NOCHECK
							SELECT @SQL = REPLACE(@SQL,'WITH CHECK ADD CONSTRAINT','WITH NOCHECK ADD CONSTRAINT')
							EXEC MD3.InsertLogMessage 
								@Message = 'Could not create as TRUSTed, creating as UNTRUSTed.  Somebody really should fix this.',
								@Severity = 'W';
							EXEC MD3.ExecuteSQL @SQL;
						END;
					END;
				END CATCH 
			END;

	
END

GO

